1 Public Class FrmPURCHASEORDERADD
2 'Dim tmpID As Integer
3 Dim stockID As Integer
4 Dim tmpPrevCustomer As String
5 Private Sub cmdCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdCancel.Click
6 Me.Close()
7 End Sub
8
9 Private Sub FrmPURCHASEORDERADD_FormClosing(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
10 Me.Text = "Purchase Order Detail"
11 chckapproved.Enabled = True
12 txtapproved.Enabled = True
13 cmdadd.Enabled = True
14 cmdSave.Enabled = True
15 cmddel.Enabled = True
16 cmdEdit.Enabled = True
17 End Sub
18
19 Private Sub FrmPURCHASEORDERADD_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
20 Dim i As Integer
21 Dim suppName As String
22 stockID = globalID
23 FILLComboBox("SELECT Supp_ID, SuppName FROM TBL_Suppliers", cmbsupplier)
24 If Split(Me.Text, " - ")(1) = "Add" Then
25
26 txtadd.Text = ""
27 txtdeliver.Text = "Paid after deliver"
28 txtapproved.Text = ""
29 chckapproved.Checked = 0
30 lstitems.Items.Clear()
31 If stockID > 0 Then
32 sqlSTR = "SELECT * FROM TBL_Suppliers WHERE Supp_ID = " & sqlDT.Rows(0)("Supp_ID")
33 ExecuteSQLQuery(sqlSTR)
34
35 If sqlDT.Rows.Count > 0 Then
36 cmbsupplier.Text = sqlDT.Rows(0)("Supp_ID") & " - " & sqlDT.Rows(0)("SuppName")
37 tmpPrevCustomer = cmbsupplier.Text
38 sqlSTR = "SELECT *, * FROM TBL_Purchase_Detail " & _
39 "INNER JOIN TBL_Category_Item_File ON TBL_Purchase_Detail.Item_ID = TBL_Category_Item_File.Item_ID " & _
40 "WHERE Purchase_ID =" & stockID & _
41 " AND TBL_Category_Item_File.Item_ID =" & globalID2
42 ExecuteSQLQuery(sqlSTR)
43 If sqlDT.Rows.Count > 0 Then
44 lstitems.Items.Add(sqlDT.Rows(0)("Item_ID"))
45 lstitems.Items(lstitems.Items.Count - 1).SubItems.Add(sqlDT.Rows(0)("Purchase_Detail_ID"))
46 lstitems.Items(lstitems.Items.Count - 1).SubItems.Add(R_Change(sqlDT.Rows(0)("Item_Name")))
47 lstitems.Items(lstitems.Items.Count - 1).SubItems.Add(R_Change(sqlDT.Rows(0)("Item_Description")))
48 lstitems.Items(lstitems.Items.Count - 1).SubItems.Add(sqlDT.Rows(0)("Item_QTY"))
49 End If
50 End If
51 End If
52 Else
53 sqlSTR = "SELECT TBL_Suppliers.Supp_ID, TBL_Suppliers.Suppname, * " & _
54 " FROM (TBL_Suppliers " & _
55 " INNER JOIN TBL_Purchase_Order ON TBL_Suppliers.Supp_ID = TBL_Purchase_Order.Supp_ID) " & _
56 " WHERE TBL_Purchase_Order.Purchase_ID = " & stockID
57
58 ExecuteSQLQuery(sqlSTR)
59 txtadd.Text = R_Change(sqlDT.Rows(0)("Address"))
60 txtdeliver.Text = R_Change(sqlDT.Rows(0)("Delivery_Term"))
61 dtpurchase.Value = sqlDT.Rows(0)("Purchased_Date")
62 dtapprove.Value = sqlDT.Rows(0)("Received_Date")
63
64 If sqlDT.Rows(0)("Approved") = "Yes" Then
65 'MsgBox("Yes")
66 chckapproved.Checked = 1
67 chckapproved.Enabled = False
68 txtapproved.Enabled = False
69 cmdadd.Enabled = False
70 cmdSave.Enabled = False
71 cmddel.Enabled = False
72 cmdEdit.Enabled = False
73 Else
74 chckapproved.Checked = 0
75 End If
76
77 suppName = sqlDT.Rows(0)("Supp_ID") & " - " & sqlDT.Rows(0)("Suppname")
78 lstitems.Items.Clear()
79 ' sqlSTR = "SELECT TBL_Purchase_Detail.Item_ID as 'ID', TBL_Purchase_Detail.Purchase_Detail_ID as 'Detail ID', TBL_Category_Item_File.Item_Name as 'Name', TBL_Category_Item_File.Item_Description as 'Description', TBL_Purchase_Detail.Item_QTY as 'Quantity' " & _
80 ' " FROM TBL_Purchase_Detail INNER JOIN TBL_Category_Item_File ON TBL_Purchase_Detail.Item_ID = TBL_Category_Item_File.Item_ID " & _
81 ' " WHERE TBL_Purchase_Detail.Purchase_ID = " & stockID
82 ' FillListView(ExecuteSQLQuery(sqlSTR), lstitems, 1)
83 sqlSTR = "SELECT *, * " & _
84 " FROM TBL_Purchase_Detail INNER JOIN TBL_Category_Item_File ON TBL_Purchase_Detail.Item_ID = TBL_Category_Item_File.Item_ID " & _
85 " WHERE TBL_Purchase_Detail.Purchase_ID = " & stockID
86 ExecuteSQLQuery(sqlSTR)
87 For i = 0 To sqlDT.Rows.Count - 1
88 lstitems.Items.Add(sqlDT.Rows(i)("Item_ID"))
89 lstitems.Items(lstitems.Items.Count - 1).SubItems.Add(sqlDT.Rows(i)("Purchase_Detail_ID"))
90 lstitems.Items(lstitems.Items.Count - 1).SubItems.Add(R_Change(sqlDT.Rows(i)("Item_Name")))
91 lstitems.Items(lstitems.Items.Count - 1).SubItems.Add(R_Change(sqlDT.Rows(i)("Item_Description")))
92 lstitems.Items(lstitems.Items.Count - 1).SubItems.Add(sqlDT.Rows(i)("Item_QTY"))
93 Next
94 cmbsupplier.SelectedItem = suppName
95 End If
96 End Sub
97
98 Private Sub cmdadd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdadd.Click
99 'FrmPURCHASEORDERLIST.ShowDialog()
100 If cmbsupplier.Text <> "" Then
101 FrmPURCHASEORDERDATA.ShowDialog()
102 Else
103 MsgBox("No Supplier Name Selected !", MsgBoxStyle.Information, "Sales and Inventory")
104 End If
105 End Sub
106
107 Private Sub cmddel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmddel.Click
108 lstitems.Focus()
109 If lstitems.Items.Count > 0 Then
110 lstitems.FocusedItem.Remove()
111 End If
112
113 End Sub
114
115 Private Sub cmdSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSave.Click
116 Dim i As Integer
117 Dim xTotal As Double
118 If cmbsupplier.Text = "" Then
119 MsgBox("Input Supplier Name", MsgBoxStyle.Information, "Sales and Inventory")
120 Exit Sub
121 End If
122 If chckapproved.Checked Then
123 If lstitems.Items.Count <= 0 Then
124 MsgBox("Can't Approved The Order, No Details Had Found!", MsgBoxStyle.Information, "Sales and Inventory")
125 Exit Sub
126 End If
127 End If
128 For i = 0 To lstitems.Items.Count - 1
129 'MsgBox(lstitems.Items(i).SubItems(5).Text & lstitems.Items(i).SubItems(6).Text)
130 'xTotal = xTotal + CDbl(lstitems.Items(i).SubItems(4).Text) * CDbl(lstitems.Items(i).SubItems(5).Text)
131 sqlSTR = "SELECT * FROM TBL_Category_Item_File WHERE Item_ID =" & lstitems.Items(i).Text
132 ExecuteSQLQuery(sqlSTR)
133 If sqlDT.Rows.Count > 0 Then
134 xTotal = xTotal + CDbl(sqlDT.Rows(0)("Item_Price")) * CDbl(lstitems.Items(i).SubItems(4).Text)
135 End If
136 Next
137 If Split(Me.Text, " - ")(1) = "Add" Then
138 'ADD
139 sqlSTR = "INSERT INTO TBL_Purchase_Order (Supp_ID, Address, Delivery_Term, Approved, Purchased_Date, Received_Date, Direct_Input, Purchase_Total) " & _
140 " VALUES (" & Split(cmbsupplier.Text, " - ")(0) & ", " _
141 & "'" & R_eplace(txtadd.Text) & "', " _
142 & "'" & R_eplace(txtdeliver.Text) & "', " _
143 & "'" & IIf(chckapproved.Checked = True, "Yes", "No") & "', " _
144 & "'" & Format(dtpurchase.Value, "MM/dd/yyyy") & "', " _
145 & "'" & Format(dtapprove.Value, "MM/dd/yyyy") & "', " _
146 & "'" & "No" & "', " _
147 & xTotal & ")"
148 ExecuteSQLQuery(sqlSTR)
149 sqlSTR = "SELECT * FROM TBL_Purchase_Order ORDER BY Purchase_ID DESC"
150 ExecuteSQLQuery(sqlSTR)
151 stockID = sqlDT.Rows(0)("Purchase_ID")
152 'MsgBox(sqlDT.Rows(0)("purchase_Id"))
153 'lstitems.Items.Clear()
154 For i = 0 To lstitems.Items.Count - 1
155 sqlSTR = "SELECT * FROM TBL_Category_Item_File WHERE Item_ID =" & lstitems.Items(i).Text
156 ' sqlSTR = "SELECT * FROM TBL_Suppliers_Product WHERE Item_ID =" & lstitems.Items(i).Text & _
157 ' " AND Supp_ID =" & Split(cmbsupplier.Text, " - ")(0)
158 ExecuteSQLQuery(sqlSTR)
159 If sqlDT.Rows.Count > 0 Then
160
161 sqlSTR = "INSERT INTO TBL_Purchase_Detail (Purchase_ID, Item_ID, Item_Qty, Item_Price, Total_Price, Unit_Measure) " & _
162 " VALUES (" & stockID & ", " _
163 & lstitems.Items(i).Text & ", " _
164 & lstitems.Items(i).SubItems(4).Text & ", " _
165 & CDbl(sqlDT.Rows(0)("Item_Price")) & ", " _
166 & CDbl(lstitems.Items(i).SubItems(4).Text) * CDbl(sqlDT.Rows(0)("Item_Price")) & ", " _
167 & "'" & sqlDT.Rows(0)("Unit_Measure") & "')"
168 ExecuteSQLQuery(sqlSTR)
169 '& lstitems.Items(i).SubItems(4).Text & ", " _
170 'CDbl(lstitems.Items(i).SubItems(4).Text) & ", " _
171 End If
172
173 Next
174 Audit_Trail(xUser_ID, TimeOfDay, "Add New Purchase Order Stocks")
175 Else
176 'EDIT
177 sqlSTR = "DELETE FROM TBL_Purchase_Detail WHERE Purchase_ID =" & stockID
178 ExecuteSQLQuery(sqlSTR)
179
180 sqlSTR = "UPDATE TBL_Purchase_Order SET Supp_ID =" & Split(cmbsupplier.Text, " - ")(0) & ", " _
181 & "Address ='" & R_eplace(txtadd.Text) & "', " _
182 & "Delivery_Term ='" & R_eplace(txtdeliver.Text) & "', " _
183 & "Approved ='" & IIf(chckapproved.Checked = True, "Yes", "No") & "', " _
184 & "Purchased_Date ='" & Format(dtpurchase.Value, "MM/dd/yyyy") & "', " _
185 & "Received_Date ='" & Format(dtapprove.Value, "MM/dd/yyyy") & "' WHERE Purchase_ID =" & stockID
186 ExecuteSQLQuery(sqlSTR)
187
188 For i = 0 To lstitems.Items.Count - 1
189 ' sqlSTR = "INSERT INTO TBL_Purchase_Detail (Purchase_ID, Item_ID,Item_Qty, Item_Price, Total_Price, Unit_Measure) " & _
190 ' " VALUES (" & stockID & ", " _
191 ' & lstitems.Items(i).Text & ", " _
192 ' & lstitems.Items(i).SubItems(6).Text & ", " _
193 ' & lstitems.Items(i).SubItems(4).Text & ", " _
194 ' & CDbl(lstitems.Items(i).SubItems(6).Text) * CDbl(lstitems.Items(i).SubItems(4).Text) & ", " _
195 ' & "'" & lstitems.Items(i).SubItems(7).Text & "')"
196 ' 'MsgBox(lstitems.Items(i).SubItems(4).Text)
197 ' ExecuteSQLQuery(sqlSTR)
198 sqlSTR = "SELECT * FROM TBL_Category_Item_File WHERE Item_ID =" & lstitems.Items(i).Text
199 ExecuteSQLQuery(sqlSTR)
200 If sqlDT.Rows.Count > 0 Then
201
202 sqlSTR = "INSERT INTO TBL_Purchase_Detail (Purchase_ID, Item_ID, Item_Qty, Item_Price, Total_Price, Unit_Measure) " & _
203 " VALUES (" & stockID & ", " _
204 & lstitems.Items(i).Text & ", " _
205 & lstitems.Items(i).SubItems(4).Text & ", " _
206 & CDbl(sqlDT.Rows(0)("Item_Price")) & ", " _
207 & CDbl(lstitems.Items(i).SubItems(4).Text) * CDbl(sqlDT.Rows(0)("Item_Price")) & ", " _
208 & "'" & sqlDT.Rows(0)("Unit_Measure") & "')"
209 ExecuteSQLQuery(sqlSTR)
210 End If
211 Next
212 Audit_Trail(xUser_ID, TimeOfDay, "Edit Purchase Order Stocks")
213 End If
214
215 'refresh list
216 sqlSTR = "SELECT TBL_Purchase_Detail.Item_ID as 'ID', TBL_Purchase_Detail.Purchase_Detail_ID as 'Detail ID', TBL_Category_Item_File.Item_Name as 'Name', TBL_Category_Item_File.Item_Description as 'Description / Item Number', TBL_Purchase_Detail.Item_QTY as 'Quantity' " & _
217 " FROM TBL_Purchase_Detail INNER JOIN TBL_Category_Item_File ON TBL_Purchase_Detail.Item_ID = TBL_Category_Item_File.Item_ID " & _
218 " WHERE TBL_Purchase_Detail.Purchase_ID = " & stockID
219 FillListView(ExecuteSQLQuery(sqlSTR), lstitems, 1)
220
221 'Update Current Stocks
222 If chckapproved.Checked Then
223 If lstitems.Items.Count > 0 Then
224 For i = 0 To lstitems.Items.Count - 1
225 sqlSTR = "SELECT * " & _
226 " FROM TBL_Stocks_Balances INNER JOIN TBL_Purchase_Detail ON TBL_Stocks_Balances.Item_ID = TBL_Purchase_Detail.Item_ID " & _
227 " WHERE TBL_Purchase_Detail.Purchase_Detail_ID = " & lstitems.Items(i).SubItems(1).Text
228 ExecuteSQLQuery(sqlSTR)
229 If sqlDT.Rows.Count > 0 Then
230 'Already Exists
231 sqlSTR = "SELECT * FROM TBL_Category_Item_File WHERE Item_ID =" & lstitems.Items(i).Text
232 ExecuteSQLQuery(sqlSTR)
233 'MsgBox(sqlDT.Rows.Count & " HERE ")
234 If sqlDT.Rows.Count > 0 Then
235
236 sqlSTR = "UPDATE TBL_Stocks_Balances SET Item_Qty = Item_QTY + " & lstitems.Items(i).SubItems(4).Text & ", " _
237 & "PASSWORD_INPUTED ='" & "No" & "', " _
238 & "DIRECT_INPUT ='" & "No" & "', " _
239 & "Unit_Measure ='" & sqlDT.Rows(0)("Unit_Measure") & "'" _
240 & " WHERE Item_ID =" & lstitems.Items(i).Text
241 ExecuteSQLQuery(sqlSTR)
242 End If
243 Else
244 'No current data
245 sqlSTR = "SELECT * FROM TBL_Category_Item_File WHERE Item_ID =" & lstitems.Items(i).Text
246 ExecuteSQLQuery(sqlSTR)
247 If sqlDT.Rows.Count > 0 Then
248 sqlSTR = "INSERT INTO TBL_Stocks_Balances (Item_ID, Item_Description, Item_Price, Item_Barcode, Item_QTY, PASSWORD_INPUTED, DIRECT_INPUT, Unit_Measure) VALUES (" & lstitems.Items(i).Text & ", " _
249 & "'" & R_eplace(lstitems.Items(i).SubItems(3).Text) & "', " _
250 & CDbl(sqlDT.Rows(0)("Item_Price")) & ", " _
251 & sqlDT.Rows(0)("Item_Barcode") & ", " _
252 & lstitems.Items(i).SubItems(4).Text & ", " _
253 & "'" & "No" & "', " _
254 & "'" & "No" & "', " _
255 & "'" & sqlDT.Rows(0)("Unit_Measure") & "')"
256 ExecuteSQLQuery(sqlSTR)
257 '&lstitems.Items(i).SubItems(4).Text & ", " _
258 '& lstitems.Items(i).SubItems(5).Text & ", " _
259 '& "'" & lstitems.Items(i).SubItems(7).Text & "')"
260 End If
261 End If
262 Next
263 End If
264 End If
265
266 MsgBox("Record Successfuly Updated !", MsgBoxStyle.Information, "Sales and Inventory")
267 sqlSTR = "SELECT Purchase_ID as 'Purchase ID', TBL_Suppliers.SuppName as 'Supplier Name', Delivery_Term as 'Delivery Term', Purchased_Date as 'Purchase Date', Approved" & _
268 " FROM TBL_Purchase_Order INNER JOIN TBL_Suppliers ON TBL_Purchase_Order.Supp_ID = TBL_Suppliers.Supp_ID " & _
269 " WHERE TBL_Purchase_Order.Purchased_Date ='" & Format(Now, "MM/dd/yyyy") & "'" & _
270 " ORDER BY Purchase_ID"
271
272 FillListView(ExecuteSQLQuery(sqlSTR), FrmPURCHASEORDER.listorder, 0)
273 With FrmPURCHASEORDER.listorder
274 For i = 0 To .Items.Count - 1
275 If .Items(i).SubItems(4).Text = "Yes" Then
276 .Items(i).ForeColor = Color.Brown
277 Else
278 .Items(i).ForeColor = Color.Black
279 End If
280 Next
281 End With
282
283 'check critical
284 sqlSTR = "SELECT TBL_Category_Item_File.Item_ID as 'ID', Replace(Replace(Item_Name,'$.$',''''),'$..$',',') as 'Name', TBL_Category_Item_File.Item_Description as 'Description / Item Number', TBL_Stocks_Balances.Item_Price as 'Price', Item_Reorder_Point as 'Reorder Point', Item_QTY as 'CURRENT STOCKS' " & _
285 "FROM TBL_Category_Item_File INNER JOIN " & _
286 "TBL_Stocks_Balances ON TBL_Category_Item_File.Item_ID = TBL_Stocks_Balances.Item_ID " & _
287 "WHERE TBL_Stocks_Balances.Item_QTY <= Item_Reorder_Point"
288 ExecuteSQLQuery(sqlSTR)
289 With MDIMain
290 If sqlDT.Rows.Count > 0 Then
291 MsgBox("A Product(s) reach its critical level !!", MsgBoxStyle.Exclamation, "Sales and Inventory")
292 .tmrcritical.Enabled = True
293 Else
294 .tmrcritical.Enabled = False
295 .cmdProductReorder.Enabled = True
296 .cmdProductReorder.ForeColor = Color.Black
297 End If
298 End With
299 Me.Close()
300 End Sub
301
302 Private Sub cmbsupplier_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmbsupplier.SelectedIndexChanged
303 Dim I As Integer
304 'Dim tmpPrevCustomer As String
305 'If Split(Me.Text, " - ")(1) = "Add" Then
306 ' If Split(cmbsupplier.Text, " - ")(0) <> "" Then
307 ' sqlSTR = "SELECT * FROM TBL_Suppliers WHERE Supp_ID =" & Split(cmbsupplier.Text, " - ")(0)
308 ' ExecuteSQLQuery(sqlSTR)
309 ' If sqlDT.Rows.Count > 0 Then
310 ' txtadd.Text = sqlDT.Rows(0)("SuppAdd")
311 ' End If
312 ' End If
313 ' End If
314 If Split(cmbsupplier.Text, " - ")(0) <> "" Then
315 sqlSTR = "SELECT * FROM TBL_Suppliers WHERE Supp_ID =" & Split(cmbsupplier.Text, " - ")(0)
316 ExecuteSQLQuery(sqlSTR)
317 If sqlDT.Rows.Count > 0 Then
318 'replace(replace(suppadd,'$.$',''''),'$..$',',')
319 txtadd.Text = Replace(Replace(sqlDT.Rows(0)("SuppAdd"), "$.$", "'"), "$..$", ",")
320
321 'tmpPrevCustomer = txtadd.Text
322 End If
323 If globalID > 0 Then
324 'sqlSTR = "SELECT *, * FROM TBL_Purchase_Detail " & _
325 ' "INNER JOIN TBL_Category_Item_File ON TBL_Purchase_Detail.Item_ID = TBL_Category_Item_File.Item_ID " & _
326 ' "WHERE Purchase_ID =" & stockID
327 'MsgBox(sqlSTR)
328 'ExecuteSQLQuery(sqlSTR)
329
330 For I = 0 To lstitems.Items.Count - 1
331 sqlSTR = "SELECT * FROM TBL_Suppliers_Product " & _
332 "WHERE Supp_ID =" & Split(cmbsupplier.Text, " - ")(0) & _
333 " AND Item_ID =" & lstitems.Items(I).Text
334 ExecuteSQLQuery(sqlSTR)
335 If sqlDT.Rows.Count <= 0 Then
336 MsgBox("Selected supplier does not have the precise item ", MsgBoxStyle.Exclamation, "Sales and Inventory")
337 cmbsupplier.Text = tmpPrevCustomer
338 Exit Sub
339 'lstitems.Items(I).Remove()
340 'Else
341 ' For x = 0 To lstitems.Items.Count - 1
342 ' If lstitems.Items(x).Text = sqlDT.Rows(0)("Item_ID") Then
343 '
344 ' End If
345 ' Next
346 End If
347 Next
348 tmpPrevCustomer = cmbsupplier.Text
349 'sqlSTR = ""
350 End If
351 End If
352 End Sub
353
354 Private Sub cmdEdit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdEdit.Click
355 With FrmPURCHASEORDERDATA_EDIT
356 If lstitems.Items.Count > 0 Then
357 lstitems.Focus()
358 .txtid.Text = lstitems.FocusedItem.Text
359 .txtdtl.Text = lstitems.FocusedItem.SubItems(1).Text
360 .txtname.Text = lstitems.FocusedItem.SubItems(2).Text
361 .txtdesc.Text = lstitems.FocusedItem.SubItems(3).Text
362 '.txtprice.Text = lstitems.FocusedItem.SubItems(4).Text
363 '.txtbarcode.Text = lstitems.FocusedItem.SubItems(5).Text
364 .txtqty.Text = lstitems.FocusedItem.SubItems(4).Text
365 '.txtunit.Text = lstitems.FocusedItem.SubItems(7).Text
366 .ShowDialog()
367 End If
368
369 End With
370 End Sub
371
372
373
374 Private Sub chckapproved_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles chckapproved.CheckedChanged
375 If chckapproved.Checked Then
376 txtapproved.Text = username
377 Else
378 txtapproved.Text = ""
379 End If
380 End Sub
381
382 Private Sub txtadd_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtadd.TextChanged
383
384 End Sub
385 End Class
